from typing import Optional
from fastapi import APIRouter, Depends, Query
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme

data_my_safe_01 = APIRouter(prefix="/material", tags=["储物柜管理"], dependencies=[Depends(oauth2_scheme)])


class CabinetSafetySearch(PaginationBase):
    # 筛选条件
    fsiteNo: str = Field("", description="站点编号")
    ftakedateStart: str = Field("2000-01-01", description="开始日期(yyyy-MM-dd)，默认'2000-01-01'")
    ftakedateEnd: str = Field("2000-01-01", description="结束日期(yyyy-MM-dd)，默认'2000-01-01'")
    fpointNo: str = Field("", description="点位编号")
    fuseuser: str = Field("", description="使用人")
    fbillno: str = Field("", description="单据编号")


@data_my_safe_01.get("/useDetail/list", summary="储物柜使用安全")
def data_my_safe_01_get(safe: CabinetSafetySearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from ( SELECT e.fbillno, e.frfidbarcode, e.fgoodno, v.fsite_no, v.fpoint_no, v.fuseuser"
    sql = sql + " , to_char(v.fplanusedate,'yyyy-mm-dd hh24:mi:ss') fplanusedate, s.fequipmenttag, '使用量超标' warringInfo "
    sql = sql + " FROM i_tc_use_detail e"
    sql = sql + " left join i_tc_use_head v on e.fbillno = v.fbillno "
    sql = sql + " left join lims.i_ts_cabinet s on e.fcabbarcode = s.fequipmentbarcode "
    sql = sql + " where 1=1"
    sql = sql + " and to_char(v.fplanusedate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
    param["ftakedateStart"] = safe.ftakedateStart
    param["ftakedateEnd"] = safe.ftakedateEnd
    sql = sql + " and e.fuseqty > e.fretqty"
    sql = sql + " and e.fretqty>0"
    if safe.fsiteNo != "":
        sql = sql + " and v.fsite_no= :fsiteNo "
        param["fsiteNo"] = safe.fsiteNo
    if safe.fpointNo != "":
        sql = sql + " and v.fpoint_no= :fpointNo "
        param["fpointNo"] = safe.fpointNo
    if safe.fbillno != "":
        sql = sql + " and v.fbillno= :fbillno "
        param["fbillno"] = safe.fbillno
    if safe.fuseuser != "":
        sql = sql + " and v.fuseuser= :fuseuser "
        param["fuseuser"] = safe.fuseuser
    sql = sql + " UNION "
    sql = sql + " select '' fbillno, h.frfidbarcode, l.fgoodno, h.fsite_no, i.fpoint_no, '' fuseuser, to_char(h.fcheckdate,'yyyy-mm-dd hh24:mi:ss')  fplanusedate, i.fequipmenttag, h.fmessage warringInfo"
    sql = sql + " from i_day_report h"
    sql = sql + " left join lims.i_ts_cabinet i on i.fequipmentbarcode = h.fcabbarcode"
    sql = sql + " left join i_warehouse_inv k on k.frfidbarcode = h.frfidbarcode and k.fstate < 3"
    sql = sql + " left join i_ts_good_info l on k.fgoodno = l.fgoodno"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(h.fcheckdate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
    sql = sql + " and h.ftype = 102"
    if safe.fsiteNo != "":
        sql = sql + " and h.fsite_no= :fsiteNo "
    if safe.fpointNo != "":
        sql = sql + " and i.fpoint_no= :fpointNo "
    sql = sql + " )"
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "SELECT e.fbillno, e.frfidbarcode, e.fgoodno, v.fsite_no, v.fpoint_no, v.fuseuser"
    sql = sql + " , to_char(v.fplanusedate,'yyyy-mm-dd hh24:mi:ss') fplanusedate, s.fequipmenttag, '使用量超标' warringInfo "
    sql = sql + " FROM i_tc_use_detail e"
    sql = sql + " left join i_tc_use_head v on e.fbillno = v.fbillno "
    sql = sql + " left join lims.i_ts_cabinet s on e.fcabbarcode = s.fequipmentbarcode "
    sql = sql + " where 1=1"
    sql = sql + " and to_char(v.fplanusedate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
    param["ftakedateStart"] = safe.ftakedateStart
    param["ftakedateEnd"] = safe.ftakedateEnd
    sql = sql + " and e.fuseqty > e.fretqty"
    sql = sql + " and e.fretqty>0"
    if safe.fsiteNo != "":
        sql = sql + " and v.fsite_no= :fsiteNo "
        param["fsiteNo"] = safe.fsiteNo
    if safe.fpointNo != "":
        sql = sql + " and v.fpoint_no= :fpointNo "
        param["fpointNo"] = safe.fpointNo
    if safe.fbillno != "":
        sql = sql + " and v.fbillno= :fbillno "
        param["fbillno"] = safe.fbillno
    if safe.fuseuser != "":
        sql = sql + " and v.fuseuser= :fuseuser "
        param["fuseuser"] = safe.fuseuser
    sql = sql + " UNION "
    sql = sql + " select '' fbillno, h.frfidbarcode, l.fgoodno, h.fsite_no, i.fpoint_no, '' fuseuser, to_char(h.fcheckdate,'yyyy-mm-dd hh24:mi:ss')  fplanusedate, i.fequipmenttag, h.fmessage warringInfo"
    sql = sql + " from i_day_report h"
    sql = sql + " left join lims.i_ts_cabinet i on i.fequipmentbarcode = h.fcabbarcode"
    sql = sql + " left join i_warehouse_inv k on k.frfidbarcode = h.frfidbarcode and k.fstate < 3"
    sql = sql + " left join i_ts_good_info l on k.fgoodno = l.fgoodno"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(h.fcheckdate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
    sql = sql + " and h.ftype = 102"
    if safe.fsiteNo != "":
        sql = sql + " and h.fsite_no= :fsiteNo "
    if safe.fpointNo != "":
        sql = sql + " and i.fpoint_no= :fpointNo "
    if safe.order == "desc":
        sql = sql + " order by  fplanusedate desc "
    else:
        sql = sql + " order by  fplanusedate "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = safe.limit
    param["offset"] = safe.offset
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['frfidbarcode'] = data[key][1]
        d1['fgoodno'] = data[key][2]
        d1['fsiteNo'] = data[key][3]
        d1['fpointNo'] = data[key][4]
        d1['fuseuser'] = data[key][5]
        d1['fplanusedate'] = data[key][6]
        d1['fequipmenttag'] = data[key][7]
        d1['warringInfo'] = data[key][8]
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": safe.limit,
                 "page": safe.pageIndex,
                 "pages": (count + safe.limit - 1) // safe.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)
